﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using AcadPos.Entity;
using TrustX.Dados;
using System.Data;

namespace AcadPos.Data
{

    public class DBEquivalencia
    {
        #region Inserir
        public bool inserir(Equivalencia equivalencia, string usuario)
        {
            string SQL = @"INSERT INTO TB_EQUIVALENCIA
                              (
                                ID_MATRIZ_CURRICULAR_DISC,
                                ID_MATRIZ_CURRICULAR_EQUIVALENTE,
                                DATA_CADASTRO
                              )
                            VALUES
                              (
                                @ID_MATRIZ_CURRICULAR_DISC,
                                @ID_MATRIZ_CURRICULAR_EQUIVALENTE,
                                GETDATE()
                              )";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_MATRIZ_CURRICULAR_DISC", SqlDbType.Int, equivalencia.IdMatrizCurricularDisc);
            db.AddParam("@ID_MATRIZ_CURRICULAR_EQUIVALENTE", SqlDbType.Int, equivalencia.IdMatrizCurricularEquivalencia);
           
            //return db.ExecuteNonQuery();

            bool inseriu = db.ExecuteNonQuery();

            if (inseriu)
            {
                SQL = @"INSERT INTO TB_AUDITORIA
                  (
                    ACAO,
                    TABELA,
                    DESCRICAO,
                    USUARIO,
                    DATA                                    
                  )
                VALUES
                  (                                     
                    'INSERT',
                    'TB_EQUIVALENCIA',
                    ((SELECT CAST(MAX(ID_EQUIVALENCIA) AS VARCHAR) FROM TB_EQUIVALENCIA)
                    + ' | ' + CAST(@ID_MATRIZ_CURRICULAR_DISC AS VARCHAR)
                    + ' | ' + CAST(@ID_MATRIZ_CURRICULAR_EQUIVALENTE AS VARCHAR)),                        
                    UPPER(@USUARIO),
                    GETDATE()
                  )";
                db = new DataAccess(SQL);
                db.AddParam("@ID_MATRIZ_CURRICULAR_DISC", SqlDbType.Int, equivalencia.IdMatrizCurricularDisc);
                db.AddParam("@ID_MATRIZ_CURRICULAR_EQUIVALENTE", SqlDbType.VarChar, equivalencia.IdMatrizCurricularEquivalencia);
                db.AddParam("@USUARIO", SqlDbType.VarChar, usuario);
            }
            db.ExecuteNonQuery();
            return inseriu;
        }
        #endregion

        #region Excluir
        public bool excluir(Equivalencia equivalencia, string usuario)
        {
            string SQL = @"DELETE 
                                FROM   TB_EQUIVALENCIA
                                WHERE  ID_EQUIVALENCIA = @ID_EQUIVALENCIA";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_EQUIVALENCIA", SqlDbType.Int, equivalencia.IdEquivalencia);
            db.AddParam("@ID_MATRIZ_CURRICULAR_DISC", SqlDbType.Int, equivalencia.IdMatrizCurricularDisc);
            db.AddParam("@ID_MATRIZ_CURRICULAR_EQUIVALENTE", SqlDbType.VarChar, equivalencia.IdMatrizCurricularEquivalencia);
            //return db.ExecuteNonQuery();

            bool excluiu = db.ExecuteNonQuery();

            if (excluiu)
            {
                SQL = @"INSERT INTO TB_AUDITORIA
                  (
                    ACAO,
                    TABELA,
                    DESCRICAO,
                    USUARIO,
                    DATA                                    
                  )
                VALUES
                  (                                     
                    'DELETE',
                    'TB_EQUIVALENCIA',
                    (CAST(@ID_EQUIVALENCIA AS VARCHAR)
                    + ' | ' + CAST(@ID_MATRIZ_CURRICULAR_DISC AS VARCHAR)
                    + ' | ' + CAST(@ID_MATRIZ_CURRICULAR_EQUIVALENTE AS VARCHAR)),                        
                    UPPER(@USUARIO),
                    GETDATE()
                  )";

                db = new DataAccess(SQL);
                
                db.AddParam("@ID_EQUIVALENCIA", SqlDbType.Int, equivalencia.IdEquivalencia);
                db.AddParam("@ID_MATRIZ_CURRICULAR_DISC", SqlDbType.Int, equivalencia.IdMatrizCurricularDisc);
                db.AddParam("@ID_MATRIZ_CURRICULAR_EQUIVALENTE", SqlDbType.VarChar, equivalencia.IdMatrizCurricularEquivalencia);
                db.AddParam("@USUARIO", SqlDbType.VarChar, usuario);
            }
            db.ExecuteNonQuery();
            return excluiu;
        }
        #endregion

        #region Lista Disciplinas Equivalentes
        public DataTable listarDisciplinasEquivalentes(Equivalencia equivalencia)
        {
            string SQL = @"SELECT  td.ID_DISCIPLINA,
                                   td.DESCRICAO AS DESCRICAO_DISCIPLINA,
                                   tmc.ORDEM_MINISTRACAO,
                                   tmc.CH_DISCIPLINA,
                                   tmc.ID_MATRIZ_CURRICULAR,
                                   tmc.ID_MODULO_CURRICULO,
                                   te.ID_MATRIZ_CURRICULAR_DISC,
                                   te.ID_MATRIZ_CURRICULAR_EQUIVALENTE,
                                   te.ID_EQUIVALENCIA
                            FROM   TB_EQUIVALENCIA te
                                   INNER JOIN TB_MATRIZ_CURRICULAR tmc
                                        ON  tmc.ID_MATRIZ_CURRICULAR = te.ID_MATRIZ_CURRICULAR_EQUIVALENTE
                                   INNER JOIN TB_DISCIPLINA td
                                        ON  td.ID_DISCIPLINA = tmc.ID_DISCIPLINA
                            WHERE  te.ID_MATRIZ_CURRICULAR_DISC = @ID_MATRIZ_CURRICULAR_DISC";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_MATRIZ_CURRICULAR_DISC", SqlDbType.Int, equivalencia.IdMatrizCurricularDisc);
            return db.GetDataTable();
        }
        #endregion
    }
}
